Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Xml
Imports System.Xml.XmlElement
Imports System.Net.Mail
Imports System.Xml.Serialization
Imports System.IO
Imports System
Imports System.Globalization
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports Microsoft.SqlServer
Imports System.Data
'Imports System.Data.SqlClient
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class Service
     Inherits System.Web.Services.WebService
    Dim connect As New Connection()
    Dim cn As New Connection
    <WebMethod()> _
    Public Function HelloWorld() As String
        Return "Hello World"
    End Function
    <WebMethod()> _
    Public Function GetItemInfo(ByVal CountryCode As String, ByVal DBName As String) As DataTable
        Try
            'If Connection.bConnect = False Then
            '    connect.setDB()
            '    If Not connect.connectDB() Then
            '        Return Nothing
            '    End If
            'End If
            'Dim oRecordSet As SAPbobsCOM.Recordset
            'oRecordSet = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Dim Sqlstr As String = "SELECT isnull([ID],'') [ID],isnull([ItemCode],'') [ItemCode],isnull([ItemName],'') [ItemName],isnull([PrchseItem],'') [PrchseItem],isnull([SellItem],'') [SellItem],isnull([InvntItem],'') [InvntItem],isnull([ItmsGrpNam],'') [ItmsGrpNam],isnull([FrgnName],'') [FrgnName] ,isnull([DB],'') [DB],Isnull([Country],'') [Country],isnull([SendDate],'') [SendDate],isnull([ReceiveDate],'') [ReceiveDate],Isnull([ErrMsg],'') [ErrMsg],isnull([Source],'') [Source],isnull([Mode],'') [Mode],isnull([U_AB_CreatedDate],'') [U_AB_CreatedDate],isnull([U_AB_UpdateDate],'') [U_AB_UpdateDate], isnull([U_AB_CreatedBy],'') [U_AB_CreatedBy],isnull([U_AB_UpdatedBy],'') [U_AB_UpdatedBy], isnull([U_AB_WWCompany],'') [U_AB_WWCompany],Isnull([ManBtchNum],'') [ManBtchNum] FROM [dbo].[ItemMasterData] where Source='SAP' and ReceiveDate is null and Country='" & CountryCode & "' and Mode is not null and [DB]='" & DBName & "' order by [ID]"
            'oRecordSet.DoQuery(Sqlstr)
            'Dim xDoc As New XmlDocument
            'oRecordSet.MoveFirst()
            'xDoc.LoadXml(oRecordSet.GetAsXML())
            'Return xDoc

            Dim Dt As DataTable = cn.SAP_RunQuery(Sqlstr)
            Dt.TableName = "ItemInfo"
            Return Dt
        Catch ex As Exception
            Connection.WriteLog("Function GetItemInfo:" & ex.Message)
            Return Nothing
        End Try
    End Function
    <WebMethod()> _
    Public Function GetDBInfo(ByVal CountryCode As String) As DataTable
        Try
            'If Connection.bConnect = False Then
            '    connect.setDB()
            '    If Not connect.connectDB() Then
            '        Return Nothing
            '    End If
            'End If
            'Dim oRecordSet As SAPbobsCOM.Recordset
            'oRecordSet = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Dim Sqlstr As String = "SELECT [Code] ,[Name],isnull([U_CCode],'') U_CCode ,isnull([U_CName],'') U_CName,isnull([U_SAPUsr],'') U_SAPUsr,isnull([U_SAPPwd],'') U_SAPPwd,isnull([U_SQLUsr],'') U_SQLUsr ,isnull([U_SQLPwd],'') U_SQLPwd,isnull([U_SQLVer],'') U_SQLVer,isnull([U_Active],'') U_Active ,isnull([U_SQLSerNam],'') U_SQLSerNam,isnull([U_SAPLiceMang],'') U_SAPLiceMang FROM [dbo].[@COUNTRY] where isnull([U_Active],'') ='Y' and U_CCode='" & CountryCode & "' "
            'oRecordSet.DoQuery(Sqlstr)
            'Dim xDoc As New XmlDocument
            'oRecordSet.MoveFirst()
            'xDoc.LoadXml(oRecordSet.GetAsXML())
            Dim Dt As DataTable = cn.SAP_RunQuery(Sqlstr)
            Dt.TableName = "DBInfo"

            Return Dt
        Catch ex As Exception
            Connection.WriteLog("Function GetItemInfo:" & ex.Message)
            Return Nothing
        End Try
    End Function
    <WebMethod()> _
      Public Function UpdateItemInfo(ByVal ID As Integer, ByVal ErrorMsg As String) As String
        Try
            'If Connection.bConnect = False Then
            '    connect.setDB()
            '    If Not connect.connectDB() Then
            '        Return Nothing
            '    End If
            'End If
            'Dim oRecordSet As SAPbobsCOM.Recordset
            'oRecordSet = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Dim Sqlstr As String = "Update [dbo].[ItemMasterData] set ReceiveDate=GetDate(),[ErrMsg]='" & ErrorMsg & "' where [ID]='" & ID & "'"
            Dim Dt As DataTable = cn.SAP_RunQuery(Sqlstr)
            'oRecordSet.DoQuery(Sqlstr)
            Return "Success"
        Catch ex As Exception
            Return "Function UpdateItemInfo:" & ex.Message
            Connection.WriteLog("Function UpdateItemInfo:" & ex.Message)
        End Try
    End Function
    <WebMethod()> _
        Public Function GetPendingItemInfo(ByVal CountryCode As String) As DataTable
        Try
            'If Connection.bConnect = False Then
            '    connect.setDB()
            '    If Not connect.connectDB() Then
            '        Return Nothing
            '    End If
            'End If
            'Dim oRecordSet As SAPbobsCOM.Recordset
            'oRecordSet = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Dim Sqlstr As String = "SELECT isnull([ID],'') [ID],isnull([ItemCode],'') [Item Code],isnull([ItemName],'') [Item Name],isnull([PrchseItem],'') [Purchase Item],isnull([SellItem],'') [Sales Item],isnull([InvntItem],'') [Inventory Item],isnull([ItmsGrpNam],'') [Group Name],isnull([DB],'') [DB],Isnull([Country],'') [Country],isnull([SendDate],'') [Send Date],isnull([ReceiveDate],'') [Receive Date],Isnull([ErrMsg],'') [ErrMsg],isnull([Source],'') [Source],isnull([Mode],'') [Mode],isnull([U_AB_CreatedDate],'') [Created Date],isnull([U_AB_UpdateDate],'') [Update Date], isnull([U_AB_CreatedBy],'') [Created By],isnull([U_AB_UpdatedBy],'') [Updated By], isnull([U_AB_WWCompany],'') [Company],Isnull([ManBtchNum],'') [Manage BatchNum] FROM [dbo].[ItemMasterData] where Source='SAP' and ReceiveDate is null and Country='" & CountryCode & "' and Mode is not null"
            'oRecordSet.DoQuery(Sqlstr)
            'Dim xDoc As New XmlDocument
            'oRecordSet.MoveFirst()
            'xDoc.LoadXml(oRecordSet.GetAsXML())
            'Return xDoc

            Dim Dt As DataTable = cn.SAP_RunQuery(Sqlstr)
            Dt.TableName = "ItemInfo"
            Return Dt
        Catch ex As Exception
            Connection.WriteLog("Function GetItemInfo:" & ex.Message)
            Return Nothing
        End Try
    End Function
    <WebMethod()> _
      Public Function GetFaildeItemInfo(ByVal CountryCode As String) As DataTable
        Try
            'If Connection.bConnect = False Then
            '    connect.setDB()
            '    If Not connect.connectDB() Then
            '        Return Nothing
            '    End If
            'End If
            'Dim oRecordSet As SAPbobsCOM.Recordset
            'oRecordSet = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            'isnull([FrgnName],'') [Foreign Name] ,
            Dim Sqlstr As String = "SELECT isnull([ID],'') [ID],isnull([ItemCode],'') [Item Code],isnull([ItemName],'') [Item Name],isnull([PrchseItem],'') [Purchase Item],isnull([SellItem],'') [Sales Item],isnull([InvntItem],'') [Inventory Item],isnull([ItmsGrpNam],'') [Group Name],isnull([DB],'') [DB],Isnull([Country],'') [Country],isnull([SendDate],'') [Send Date],isnull([ReceiveDate],'') [Receive Date],Isnull([ErrMsg],'') [ErrMsg],isnull([Source],'') [Source],isnull([Mode],'') [Mode],isnull([U_AB_CreatedDate],'') [Created Date],isnull([U_AB_UpdateDate],'') [Update Date], isnull([U_AB_CreatedBy],'') [Created By],isnull([U_AB_UpdatedBy],'') [Updated By], isnull([U_AB_WWCompany],'') [Company],Isnull([ManBtchNum],'') [Manage BatchNum] FROM [dbo].[ItemMasterData] where Source='SAP' and ReceiveDate is not null and Country='" & CountryCode & "' and Mode is not null and isnull(ErrMsg,'') <> ''"
            'oRecordSet.DoQuery(Sqlstr)
            'Dim xDoc As New XmlDocument
            'oRecordSet.MoveFirst()
            'xDoc.LoadXml(oRecordSet.GetAsXML())
            'Return xDoc

            Dim Dt As DataTable = cn.SAP_RunQuery(Sqlstr)
            Dt.TableName = "ItemInfo"
            Return Dt
        Catch ex As Exception
            Connection.WriteLog("Function GetItemInfo:" & ex.Message)
            Return Nothing
        End Try
    End Function
    <WebMethod()> _
     Public Function GetSuccessItemInfo(ByVal CountryCode As String) As DataTable
        Try
            'If Connection.bConnect = False Then
            '    connect.setDB()
            '    If Not connect.connectDB() Then
            '        Return Nothing
            '    End If
            'End If
            'Dim oRecordSet As SAPbobsCOM.Recordset
            'oRecordSet = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Dim Sqlstr As String = "SELECT Top 2000 isnull([ID],'') [ID],isnull([ItemCode],'') [Item Code],isnull([ItemName],'') [Item Name],isnull([PrchseItem],'') [Purchase Item],isnull([SellItem],'') [Sales Item],isnull([InvntItem],'') [Inventory Item],isnull([ItmsGrpNam],'') [Group Name],isnull([DB],'') [DB],Isnull([Country],'') [Country],isnull([SendDate],'') [Send Date],isnull([ReceiveDate],'') [Receive Date],Isnull([ErrMsg],'') [ErrMsg],isnull([Source],'') [Source],isnull([Mode],'') [Mode],isnull([U_AB_CreatedDate],'') [Created Date],isnull([U_AB_UpdateDate],'') [Update Date], isnull([U_AB_CreatedBy],'') [Created By],isnull([U_AB_UpdatedBy],'') [Updated By], isnull([U_AB_WWCompany],'') [Company],Isnull([ManBtchNum],'') [Manage BatchNum] FROM [dbo].[ItemMasterData] where Source='SAP' and ReceiveDate is not null and Country='" & CountryCode & "' and Mode is not null and isnull(ErrMsg,'') = '' order by [ID] desc"
            'oRecordSet.DoQuery(Sqlstr)
            'Dim xDoc As New XmlDocument
            'oRecordSet.MoveFirst()
            'xDoc.LoadXml(oRecordSet.GetAsXML())
            'Return xDoc

            Dim Dt As DataTable = cn.SAP_RunQuery(Sqlstr)
            Dt.TableName = "ItemInfo"
            Return Dt
        Catch ex As Exception
            Connection.WriteLog("Function GetItemInfo:" & ex.Message)
            Return Nothing
        End Try
    End Function

End Class
